package com.scalar.forecast.forecastEntry.dao;

import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.forecast.forecastEntry.SearchField;
import com.scalar.forecast.forecastEntry.BuildNumbersComparator;
import com.scalar.forecast.forecastEntry.forms.ForecastEntrySearchForm;
import com.scalar.forecast.forecastEntry.forms.ForecastEntryForm;
import com.scalar.forecast.forecastSetup.dao.ForecastSetupDAO;
import com.scalar.forecast.forecastSetup.ForecastEntryField;
import com.scalar.forecast.forecastSetup.ForecastTimeFrame;
import com.scalar.forecast.commons.Constants;
import com.scalar.forecast.commons.Labels;
import com.scalar.forecast.utils.ForecastSqlQueryMappings;
import com.scalar.forecast.systemprefs.SystemPreferences;
import com.scalar.forecast.systemprefs.dao.SystemPrefsDAO;
import com.scalar.ScalarException;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.sql.*;

import org.apache.struts.util.LabelValueBean;
import org.apache.struts.util.MessageResources;

/**
 * User: Sujan Kumar
 * Date: Feb 11, 2005
 * Time: 12:28:34 PM
 */
public class ForecastEntrySearchDAO
{
    public static ArrayList getForecastSetupSearchFields(DBTransaction dbTransaction) throws ScalarException
    {
        ArrayList searchFieldList = new ArrayList();
        SearchField searchField = null;
        try
        {

            ArrayList setupFieldList = ForecastSetupDAO.getForecastSetupFields( dbTransaction );
            int setupFieldListSize = setupFieldList.size();
            ForecastEntryField setupField = null;
            for ( int i =0; i < setupFieldListSize; i++ )
            {
                setupField = ( ForecastEntryField ) setupFieldList.get( i );
                if ( setupField.isEntrySelected() )
                {
                    searchField = new SearchField();

                    searchField.setName( setupField.getEntryName() );
                    searchField.setSortOrder( setupField.getEntryOrder() );
                    searchField.setDisplay( setupField.isEntrySelected() );
                    searchFieldList.add ( searchField );
                }
            }
            fillSetupSearchFieldMetaData ( searchFieldList );

            //-------setting forecast time frame search fields
            ForecastTimeFrame timeframe = ForecastSetupDAO.getForecastTimeFrame( dbTransaction );
            ArrayList propValues = new ArrayList();
            LabelValueBean labelValueBean = null;
            if ( "Month".equalsIgnoreCase( timeframe.getTfName() ) )
            {
                searchField = new SearchField();
                searchField.setName( "Month" );
                searchField.setSortOrder( 5 );
                searchField.setDisplayName ( "Start Month" );
                searchField.setType ( "select" );
                searchField.setDisplay( true );
                searchField.setPropertyName( "startMonth_Quarter_Year" );
                labelValueBean = new LabelValueBean( "---Select Month---","-1");
                propValues.add ( labelValueBean );
                String months[] = { "January", "February", "March","April","May","June","July","August","September","October","November","December" };
                for ( int i=0; i < months.length; i++ )
                {
                    labelValueBean = new LabelValueBean( months[i], Integer.toString(i) );
                    propValues.add ( labelValueBean );
                }
                searchField.setPropertyValues( propValues );
                searchFieldList.add ( searchField );


                searchField = new SearchField();
                searchField.setName( "" );
                searchField.setSortOrder( 6 );
                searchField.setDisplayName ( "Year" );
                searchField.setType ( "select" );
                searchField.setDisplay( true );
                searchField.setPropertyName( "year" );
                ArrayList yearList = getYearList();
                labelValueBean = new LabelValueBean( "---Select Year---","-1" );
                yearList.add ( 0, labelValueBean );
                searchField.setPropertyValues( yearList );
                searchFieldList.add ( searchField );

                searchField = new SearchField();
                searchField.setName( "showNext" );
                searchField.setSortOrder( 7 );
                searchField.setDisplayName ( "Show Next" );
                searchField.setType ( "text" );
                searchField.setDisplay( true );
                searchField.setDisplaySize( 4 );
                searchField.setPropertyName( "showNext" );
                searchFieldList.add ( searchField );

            }
            else if ( "Quarter".equalsIgnoreCase ( timeframe.getTfName() ) )
            {
                searchField = new SearchField();
                searchField.setName( "Quarter" );
                searchField.setSortOrder( 5 );
                searchField.setDisplayName ( "Start Quarter" );
                searchField.setType ( "select" );
                searchField.setDisplay( true );
                searchField.setPropertyName( "startMonth_Quarter_Year" );
                labelValueBean = new LabelValueBean( "---Select Quarter---","-1");
                propValues.add ( labelValueBean );
                //String quarters[] = { "Quarter-1", "Quarter-2", "Quarter-3","Quarter-4" };
				String quarters[] = getCurrentYearQuarterNames ( dbTransaction );
                for ( int i=0; i < quarters.length; i++ )
                {
                    labelValueBean = new LabelValueBean( quarters[i], Integer.toString(i) );
                    propValues.add ( labelValueBean );
                }
                searchField.setPropertyValues( propValues );
                searchFieldList.add ( searchField );

                searchField = new SearchField();
                searchField.setName( "" );
                searchField.setSortOrder( 6 );
                searchField.setDisplayName ( "Year" );
                searchField.setType ( "select" );
                searchField.setDisplay( true );
                searchField.setPropertyName( "year" );
                ArrayList yearList = getYearList();
                labelValueBean = new LabelValueBean( "---Select Year---","-1" );
                yearList.add ( 0, labelValueBean );
                searchField.setPropertyValues( yearList );
                searchFieldList.add ( searchField );

                searchField = new SearchField();
                searchField.setName( "showNext" );
                searchField.setSortOrder( 7 );
                searchField.setDisplay( true );
                searchField.setDisplayName ( "Show Next" );
                searchField.setType ( "text" );
                searchField.setDisplaySize( 4 );
                searchField.setPropertyName( "showNext" );
                searchFieldList.add ( searchField );

            }
            else if ( "Year".equalsIgnoreCase ( timeframe.getTfName() ) )
            {
                searchField = new SearchField();
                searchField.setName( "Year" );
                searchField.setSortOrder( 5 );
                searchField.setDisplayName ( "Year" );
                searchField.setType ( "select" );
                searchField.setDisplay( true );
                searchField.setPropertyName( "startMonth_Quarter_Year" );
                ArrayList yearList = getYearList();
                labelValueBean = new LabelValueBean( "---Select Year---","-1" );
                yearList.add ( 0, labelValueBean );
                searchField.setPropertyValues( yearList );
                searchFieldList.add ( searchField );


                /*searchField = new SearchField();
                searchField.setName( "" );
                searchField.setSortOrder( 6 );
                searchField.setDisplayName ( "Year" );
                searchField.setType ( "select" );
                searchField.setPropertyName( "year" );
                yearList = getYearList();
                labelValueBean = new LabelValueBean( "---Select Year---","0" );
                yearList.add ( 0, labelValueBean );
                searchField.setPropertyValues( yearList );
                searchFieldList.add ( searchField );*/

                searchField = new SearchField();
                searchField.setName( "showNext" );
                searchField.setSortOrder( 7 );
                searchField.setDisplayName ( "Show Next" );
                searchField.setType ( "text" );
                searchField.setDisplay( true );
                searchField.setDisplaySize( 4 );
                searchField.setPropertyName( "showNext" );
                searchFieldList.add ( searchField );

            }
        }
        catch ( Exception e )
        {
            System.out.println ( "----Exception While Fetching the Forecast Setup Search Fields----");
            throw new ScalarException ( "Could Not Fetch Forecast Setup Search Fields" );
        }

        return searchFieldList;
    }

    private static void fillSetupSearchFieldMetaData ( ArrayList searchFieldList )
    {
        int listSize = searchFieldList.size();
        SearchField searchField = null;
        for ( int i=0; i < listSize; i++ )
        {
            searchField = ( SearchField )searchFieldList.get ( i );
            if ( "By Customer".equalsIgnoreCase( searchField.getName() ) )
            {
                searchField.setDisplayName( "Customer" );
                searchField.setType( "select" );
                searchField.setPropertyName( "custId" );
                searchField.setPropertyValue ( "-1" );
                searchField.setSortOrder ( 1 );

            }
            else if ( "By Project".equalsIgnoreCase( searchField.getName() ) )
            {
                searchField.setDisplayName( "Project" );
                searchField.setType ( "select" );
                searchField.setPropertyName( "projectId" );
                searchField.setPropertyValue ( "-1" );
                searchField.setSortOrder ( 2 );

            }
            else if ( "By Product".equalsIgnoreCase( searchField.getName() ) )
            {
                searchField.setDisplayName( "Product" );
                searchField.setType( "select" );
                searchField.setPropertyName( "productId" );
                searchField.setPropertyValue ( "-1" );
                searchField.setSortOrder ( 3 );

                /*ArrayList temp = new ArrayList();
                org.apache.struts.util.LabelValueBean labelValueBean = null;
                labelValueBean = new LabelValueBean( "product1", "prductVal");
                temp.add ( labelValueBean );
                searchField.setPropertyValues ( temp );*/

            }
            else if ( "By ODM".equalsIgnoreCase( searchField.getName() ) )
            {
                String odmLabel = MessageResources.getMessageResources( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage("label.sales.endCustomer");
                searchField.setDisplayName( odmLabel );
                searchField.setType ( "select" );
                searchField.setPropertyName( "odmId" );
                searchField.setPropertyValue ( "-1" );
                searchField.setSortOrder ( 4 );
            }
        }
    }
    public static ArrayList getAllProducts( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList products = new ArrayList();
        LabelValueBean labelValueBean = null;
        boolean success = false;
        String partNumberType =  null;
        /*labelValueBean = new LabelValueBean ( "---Select Product---","0");
        products.add ( labelValueBean );*/

        SystemPreferences systemPreferences= SystemPrefsDAO.loadSystemPreferences();
        if ( ( SystemPreferences.MANFACTURING_PART_NUMBERS ).equalsIgnoreCase( systemPreferences.getPartNumberType( ) ) ) {
               partNumberType = "PART_NUMBER";
        } else{
               partNumberType = "P_MARKETING_PART_NUMBER";
        }

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get( "forecast.forecastEntrySearch.getProducts" ) );
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                labelValueBean = new LabelValueBean( rset.getString ( partNumberType ), rset.getString ( "PRODUCT_ID") );
                products.add( labelValueBean );
            }
            pstmt.close();
            pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get ( "forecast.forecastEntrySearch.getBoardsOrKits" ) );

            //to load BOARDS
            pstmt.setString ( 1, "BOARD" );
            labelValueBean = new LabelValueBean ( "---BOARD---","0");
            products.add( labelValueBean );
            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                labelValueBean = new LabelValueBean( rset.getString ( partNumberType ), rset.getString ( "PRODUCT_ID") );
                products.add( labelValueBean );
            }


            //to load KITS
            pstmt.setString ( 1, "KIT" );
            labelValueBean = new LabelValueBean ( "---KIT---","0");
            products.add( labelValueBean );
            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                labelValueBean = new LabelValueBean( rset.getString ( partNumberType ), rset.getString ( "PRODUCT_ID") );
                products.add( labelValueBean );
            }
            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            System.out.println ( "----Exception while getting all the products-----");
            throw new ScalarException ( "Could Not Get All the Products" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException ignore ){ }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException e ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException e ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException e ) { }
            }
        }
        return products;
    }
    public static ArrayList getYearList ()
    {
        int startYear = Constants.YEAR_START;
        int endYear = Constants.YEAR_END;

        ArrayList yearList = new ArrayList ();
        for ( int year = startYear; year <= endYear; year++ )
        {
            yearList.add ( new LabelValueBean ( Integer.toString(year), Integer.toString(year) ) );
        }
        return yearList;
    }

    /**
     * Gets all projects from projsetup table.
     * @return
     * returns ArrayList Consisting of LableValueBean which contains  projectName and projectId.
     * @throws ScalarException
     * @param dbTransaction
     */
    public static ArrayList getAllProjects(DBTransaction dbTransaction) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList projects = new ArrayList();
        LabelValueBean labelValueBean = null;
        boolean success = false;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.forecastEntrySearch.getAllProjects" ) );
            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                labelValueBean = new LabelValueBean( rset.getString ( "PROJNAME"), rset.getString ( "PROJID") );
                projects.add ( labelValueBean );
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "-----Exception while getting all the projects----");
            e.printStackTrace();
            throw new ScalarException( "Could Not Load All Projects");
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException ignore ){ }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); }catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
        return projects;
    }

    /**
     * To get all the projects related to a customer.
     * @param dbTransaction
     * @param customerId
     * id of the customer
     * @return
     * returns ArrayList consisting of LabelValueBean which contains  projectName and projectId.
     * @throws ScalarException
     */
    public static ArrayList getAllProjectsForCustomer( DBTransaction dbTransaction, int customerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList projects = new ArrayList();
        LabelValueBean labelValueBean = null;
        boolean success = false;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.forecastEntrySearch.getProjectsForCustomer" ) );
            pstmt.setInt ( 1, customerId );
            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                labelValueBean = new LabelValueBean( rset.getString ( "PROJNAME"), rset.getString ( "PROJID") );
                projects.add ( labelValueBean );
            }
        }
        catch ( Exception e )
        {
            System.out.println ( "-----Exception while getting all the projects----");
            e.printStackTrace();
            throw new ScalarException( "Could Not get Projects For Customer" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException ignore ){ }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); }catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
        return projects;
    }

    /**
     * Gets all products related to a project
     * @param dbTransaction
     * @param projectId
     * id of the project
     * @return
     * returns ArrayList consisting of LableValueBean Which contains  productName and productId.
     * @throws ScalarException
     */
    public static ArrayList getAllProductsForProject(DBTransaction dbTransaction, int projectId) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList projects = new ArrayList();
        LabelValueBean labelValueBean = null;
        boolean success = false;
        String partNumberType = null;

        SystemPreferences systemPreferences= SystemPrefsDAO.loadSystemPreferences();
        if ( ( SystemPreferences.MANFACTURING_PART_NUMBERS ).equalsIgnoreCase( systemPreferences.getPartNumberType( ) ) ) {
               partNumberType = "PART_NUMBER";
        } else{
               partNumberType = "P_MARKETING_PART_NUMBER";
        }

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.forecastEntrySearch.getProductsForProject" ) );
            pstmt.setInt ( 1, projectId );
            pstmt.setString( 2, partNumberType);
            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                labelValueBean = new LabelValueBean(  rset.getString ( partNumberType), rset.getString ( "PRODID") );
                projects.add ( labelValueBean );
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "-----Exception while getting all the projects----");
            e.printStackTrace();
            throw new ScalarException( "Could Not Get Products For the Project");
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException ignore ){ }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); }catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
        return projects;
    }

    /**
     * Gets all Customers
     * @return
     * returns ArrayList consisting of LableValueBean Which contains  customerName and customerId.
     * @throws ScalarException
     * @param dbTransaction
     */
    public static ArrayList getAllCustomers (DBTransaction dbTransaction) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList customers = new ArrayList();
        LabelValueBean labelValueBean = null;
        boolean success =false;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get ( "forecast.forecastEntrySearch.getAllCustomers" ) );
            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                labelValueBean = new LabelValueBean( rset.getString ( "C_Name"), rset.getString ( "Customer_ID") );
                customers.add ( labelValueBean );
            }
            success =true;
        }
        catch ( Exception e )
        {
            System.out.println ( "---Exception while fetching customers ---" );
            e.printStackTrace();
            throw new ScalarException ( "Could Not Load Customers" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException ignore ){ }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close();} catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
        return customers;
    }

    /**
     * This method is used to know the current Quarter.
     * @return
     * returns integer representing the quarter number.<br>
     * 0 - Quarter 1 <br>
     * 1 - Quarter 2 <br>
     * 2 - Quarter 3 <br>
     * 3 - Quarter 4 <br>
     *
     * -1 - if no calendar is set for the currentYear.
     * @param dbTransaction
     */
    public static int getCurrentQuarter (DBTransaction dbTransaction) throws ScalarException
    {
        int currentQuarter = -1;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        Calendar now = Calendar.getInstance();
        int currentYear = now.get ( Calendar.YEAR ) ;
        Calendar startQuarterCalendar = Calendar.getInstance();
        Calendar endQuarterCalendar = Calendar.getInstance();
        boolean success = false;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection( );
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get ( "forecast.forecastEntrySearch.getCompanyCalendar" ) );
            pstmt.setInt ( 1, currentYear );
            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                java.sql.Date startQuarter = rset.getDate ( "CQC_Start_Date" );
                java.sql.Date endQuarter = rset.getDate ( "CQC_End_Date" );
                if ( startQuarter != null && endQuarter != null )
                {
                    startQuarterCalendar.setTime( startQuarter );
                    endQuarterCalendar.setTime ( endQuarter );
                    long startTime = startQuarterCalendar.getTimeInMillis();
                    long endTime = endQuarterCalendar.getTimeInMillis();
					now.set ( Calendar.HOUR_OF_DAY, endQuarterCalendar.get ( Calendar.HOUR_OF_DAY ) );
					now.set ( Calendar.MINUTE, endQuarterCalendar.get ( Calendar.MINUTE ) );
					now.set ( Calendar.SECOND, endQuarterCalendar.get ( Calendar.SECOND ) );
					now.set ( Calendar.MILLISECOND, endQuarterCalendar.get ( Calendar.MILLISECOND ) );
                    long nowTime = now.getTimeInMillis();
                    if ( nowTime >= startTime  && nowTime <= endTime)
                    {
                        currentQuarter = ( rset.getInt ( "CQC_Quarter_No" ) -1 );
                        break;
                    }
                }
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "---Exception while calculating the CurrentQuarter---" );
            e.printStackTrace();
            throw new ScalarException ( "Could Not get Current Quarter From Company Calendar" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException ignore ){ }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
        return currentQuarter;
    }

    /**
     * Used to get the projectName for the given projectId.
     * @param dbTransaction
     * @param projectId
     * @return
     * returns name of the project.<br>
     * returns empty String if there is no project with the given id.
     * @throws ScalarException
     */
    public static String getProjectName (DBTransaction dbTransaction, int projectId) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String projectName = "";
        boolean success = false;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.forecastEntrySearch.getProjectName" ) );
            pstmt.setInt ( 1, projectId );
            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                projectName = rset.getString ( "PROJNAME");
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "---Exception while fetching the Project Name---" );
            e.printStackTrace();
            throw new ScalarException( "Could Not get Project Name" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException ignore ){ }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) { }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
        return projectName;
    }

    /**
     * Used to get the Part Number for the given productId.
     * @param dbTransaction
     * @param productId
     * @return
     * returns partNumber for the given productId.
     * returns empty if there is no partNumber for the productId.
     * @throws ScalarException
     */
    public static String getPartNumber (DBTransaction dbTransaction, int productId) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String partNumber = "";
        boolean success = false;
        String partNumberType = null;

        SystemPreferences systemPreferences= SystemPrefsDAO.loadSystemPreferences();
        if ( ( SystemPreferences.MANFACTURING_PART_NUMBERS ).equalsIgnoreCase( systemPreferences.getPartNumberType( ) ) ) {
               partNumberType = "PART_NUMBER";
        } else{
               partNumberType = "P_MARKETING_PART_NUMBER";
        }

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get ( "forecast.forecastEntrySearch.getPartNumber" ) );
            pstmt.setInt ( 1, productId );
            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                partNumber = rset.getString( partNumberType );
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "---Exception While getting the Part Numbe---");
            e.printStackTrace();
            throw new ScalarException( "Could Not get PartNumber" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException ignore ){ }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }
        return partNumber;
    }
    public static ArrayList getAllForecastSalesPersons( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;
        ArrayList salesPersons = new ArrayList();
        LabelValueBean salesPerson = null;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.forecastEntrySearch.loadAllForecastSalesPersons" ) );
            rset = pstmt.executeQuery();
            while ( rset.next( ) )
            {
                salesPerson = new LabelValueBean( rset.getString ( "SalesPerson_Name" ), Integer.toString ( rset.getInt ( "SPERSONID" ) ) );
                salesPersons.add ( salesPerson );
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "-----Exception While Loading ForecastSalesPerons-----" );
            e.printStackTrace();
            throw new ScalarException ( "Could Not Load Forecast Sales Persons");
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException ignore ){ }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
        return salesPersons;
    }
    public static ArrayList getBuildNumbers ( DBTransaction dbTransaction, ForecastEntrySearchForm forecastEntrySearchForm ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;
        ArrayList buildNumbers = new ArrayList();
        LabelValueBean buildNumber = null;
        String searchCriteria = getSearchCriteria ( dbTransaction, forecastEntrySearchForm);
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.forecastEntrySearch.loadBuildNumbers" ) + searchCriteria );
            rset = pstmt.executeQuery();
            String buildNo = null;
            while ( rset.next() )
            {
                buildNo = rset.getString ( "BuildNo" );
                buildNumber = new LabelValueBean ( buildNo, buildNo );
                buildNumbers.add ( buildNumber );
            }
			Collections.sort ( buildNumbers, new BuildNumbersComparator ( ) );
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "---Exception While Loading Build Numbers---" );
            e.printStackTrace();
            throw new ScalarException ( "Could Not Load Build Numbers" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException ignore ){ }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
        return buildNumbers;
    }

    private static String getSearchCriteria ( DBTransaction dbTransaction, ForecastEntrySearchForm forecastEntrySearchForm ) throws ScalarException
    {
        StringBuffer searchCriteria = new StringBuffer ( "" );
        boolean isWhere = false;

        if ( forecastEntrySearchForm.getCustId() > 0 )
        {
            searchCriteria.append ( " WHERE " );
            searchCriteria.append ( " CustId = " );
            searchCriteria.append ( forecastEntrySearchForm.getCustId() );
            isWhere = true;
        }

        if ( forecastEntrySearchForm.getProjectId() > 0 )
        {
            if ( isWhere )
            {
               searchCriteria.append ( " AND " );
            }
            else
            {
                searchCriteria.append ( " WHERE " );
                isWhere = true;
            }
            searchCriteria.append ( " ProjId = " );
            searchCriteria.append ( forecastEntrySearchForm.getProjectId() );
        }
        if ( forecastEntrySearchForm.getProductId() > 0 )
        {
            if ( isWhere )
            {
               searchCriteria.append ( " AND " );
            }
            else
            {
                searchCriteria.append ( " WHERE " );
                isWhere = true;
            }
            searchCriteria.append ( " ProdId = " );
            searchCriteria.append ( forecastEntrySearchForm.getProductId() );
        }
        if ( forecastEntrySearchForm.getOdmId() > 0 )
        {
            if ( isWhere )
            {
               searchCriteria.append ( " AND " );
            }
            else
            {
                searchCriteria.append ( " WHERE " );
                isWhere = true;
            }
            searchCriteria.append ( " OdmId = " );
            searchCriteria.append ( forecastEntrySearchForm.getOdmId() );
        }
        if ( forecastEntrySearchForm.getSalesPersonId() > 0 )
        {
            if ( isWhere )
            {
               searchCriteria.append ( " AND " );
            }
            else
            {
                searchCriteria.append ( " WHERE " );
                isWhere = true;
            }
            searchCriteria.append ( " SPersonId = " );
            searchCriteria.append ( forecastEntrySearchForm.getSalesPersonId() );
        }
        if ( forecastEntrySearchForm.getBuildNo() != null && forecastEntrySearchForm.getBuildNo().trim().length() != 0 )
        {
            if ( isWhere )
            {
               searchCriteria.append ( " AND " );
            }
            else
            {
                searchCriteria.append ( " WHERE " );
                isWhere = true;
            }
            searchCriteria.append ( " BuildNo LIKE " );
            searchCriteria.append ( " '" );
			String buildNo = forecastEntrySearchForm.getBuildNo();
			buildNo = ( buildNo == null ) ? "" : buildNo.replaceAll("_","[_]").replaceAll("%","[%]");
            searchCriteria.append ( buildNo );
            searchCriteria.append ( "%' " );
        }

        ForecastTimeFrame timeFrame = ForecastSetupDAO.getForecastTimeFrame( dbTransaction );
        if ( Constants.FORECAST_TIMEFRAME_MONTH.equalsIgnoreCase( timeFrame.getTfName() ) )
        {
            if ( forecastEntrySearchForm.getStartMonth_Quarter_Year() >= 0 )
            {
                if ( isWhere )
                {
                   searchCriteria.append ( " AND " );
                }
                else
                {
                    searchCriteria.append ( " WHERE " );
                    isWhere = true;
                }
                searchCriteria.append ( " Month = " );
                searchCriteria.append ( forecastEntrySearchForm.getStartMonth_Quarter_Year() );
            }
            if ( forecastEntrySearchForm.getYear() > 0 )
            {
                if ( isWhere )
                {
                   searchCriteria.append ( " AND " );
                }
                else
                {
                    searchCriteria.append ( " WHERE " );
                    isWhere = true;
                }
                searchCriteria.append ( " Year = " );
                searchCriteria.append ( forecastEntrySearchForm.getYear() );
            }
        }
        if ( Constants.FORECAST_TIMEFRAME_QUARTER.equalsIgnoreCase( timeFrame.getTfName() ) )
        {
            if ( forecastEntrySearchForm.getStartMonth_Quarter_Year() >= 0 )
            {
                if ( isWhere )
                {
                   searchCriteria.append ( " AND " );
                }
                else
                {
                    searchCriteria.append ( " WHERE " );
                    isWhere = true;
                }
                searchCriteria.append ( " Quarter = " );
                searchCriteria.append ( forecastEntrySearchForm.getStartMonth_Quarter_Year() );
            }
            if ( forecastEntrySearchForm.getYear() > 0 )
            {
                if ( isWhere )
                {
                   searchCriteria.append ( " AND " );
                }
                else
                {
                    searchCriteria.append ( " WHERE " );
                    isWhere = true;
                }
                searchCriteria.append ( " Year = " );
                searchCriteria.append ( forecastEntrySearchForm.getYear() );
            }
        }
        if ( Constants.FORECAST_TIMEFRAME_YEAR.equalsIgnoreCase( timeFrame.getTfName() ) )
        {
            if ( forecastEntrySearchForm.getStartMonth_Quarter_Year() >= 0 )
            {
                if ( isWhere )
                {
                   searchCriteria.append ( " AND " );
                }
                else
                {
                    searchCriteria.append ( " WHERE " );
                    isWhere = true;
                }
                searchCriteria.append ( " Year = " );
                searchCriteria.append ( forecastEntrySearchForm.getStartMonth_Quarter_Year() );
            }
        }

        return searchCriteria.toString();
    }
    public static void loadFormDetailsForBuild ( DBTransaction dbTransaction, String buildNo, ForecastEntryForm forecastEntryForm )
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.forecastEntry.getBuildDetails" ) );
            pstmt.setString ( 1, buildNo );
            rset = pstmt.executeQuery();
            String prodId = null;
            String custId = null;
            String odmId = null;
            String projId = null;
            String month = null;
            String quarter = null;
            int year = -1;
            int showNext = 0;
            if ( rset.next() )
            {
                prodId = rset.getString ( "ProdId" );
                custId = rset.getString ( "CustId" );
                odmId = rset.getString ( "OdmId" );
                projId = rset.getString ( "ProjId" );
                month = rset.getString ( "Month" );
                quarter = rset.getString ( "Quarter" );
                year = rset.getInt ( "year" );
            }
            rset.close();
            pstmt.close();
            pstmt=conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.forecastEntry.getShowNextCntForBuildNo" ) );
            pstmt.setString ( 1, buildNo );
            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                showNext++;
            }
            forecastEntryForm.setShowNext( showNext );
            forecastEntryForm.setProductId( prodId == null ? -1 : Integer.parseInt ( prodId ) );
            forecastEntryForm.setCustId( custId == null ? -1 : Integer.parseInt ( custId ) );
            forecastEntryForm.setOdmId( odmId == null ? -1 : Integer.parseInt( odmId ) );
            forecastEntryForm.setProjectId( projId == null ? -1 : Integer.parseInt ( projId ) );
            if ( month == null && quarter != null )
            {
                forecastEntryForm.setStartMonth_Quarter_Year( Integer.parseInt ( quarter ) );
                forecastEntryForm.setYear ( year );
            }
            if ( month != null && quarter == null )
            {
                forecastEntryForm.setStartMonth_Quarter_Year( Integer.parseInt ( month ) );
                forecastEntryForm.setYear ( year );
            }
            if ( month == null && quarter == null )
            {
                forecastEntryForm.setStartMonth_Quarter_Year( year );
            }
           success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "--Exception while loading forecast Entry Form--" );
            e.printStackTrace();
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException ignore ){ }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
    }

    public static ArrayList getEntryDateAndEnteredByForBuildNo ( DBTransaction dbTransaction, String buildNo )
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;
        ArrayList entrydetails = new ArrayList();
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get ( "forecast.forecastEntrySearch.getBuildNoEntryDateAndEnteredBy" ) );
            pstmt.setString ( 1, buildNo );
            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                Date entryDate = rset.getDate( "EntryDate" );
                String enteredBy = rset.getString ( "UserId" );
                entrydetails.add ( entryDate );
                entrydetails.add ( enteredBy );
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "---Exception while getting the entryDate and EnteredBy details---" );
            e.printStackTrace();
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException ignore ){ }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
        return entrydetails;
    }
    public static ArrayList getAllCustomersForProject ( DBTransaction dbTransaction, int projectId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;
        ArrayList customers = new ArrayList();
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.forecastEntrySearch.getCustomersForProject" ) );
            pstmt.setInt ( 1, projectId );
            rset = pstmt.executeQuery();
            LabelValueBean customer = null;
            while ( rset.next() )
            {
                customer = new LabelValueBean( rset.getString ( "C_NAME"), rset.getString ( "CUSTID" ) );
                customers.add ( customer );
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ("------Exception while getting the customers for the project----");
            e.printStackTrace();
            throw new ScalarException ( "Could Not Get Customers For The Project" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException ignore ){ }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
        return customers;
    }
    public static ArrayList getAllProjectsForProduct ( DBTransaction dbTransaction, int productId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;
        ArrayList projects = new ArrayList();
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.forecastEntrySearch.getProjectsForProduct" ) );
            pstmt.setInt( 1, productId );
            rset = pstmt.executeQuery();
            LabelValueBean project = null;
            while ( rset.next() )
            {
                project = new LabelValueBean ( rset.getString ( "PROJNAME"), rset.getString ( "PROJID") );
                projects.add ( project );
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ("------Exception while getting projects for product---");
            e.printStackTrace();
            throw new ScalarException ( "Could Not Get Project For the Product" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException ignore ){ }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
        return projects;
    }

	/**
	 * To get the quarter names set for the company for the currentyear.
	 * @param dbTransaction
	 * @return
	 * quarter names array.
	 * @throws ScalarException
	 * throws exception when there r no quarters set for the company for the current year.
	 */
	public static String[] getCurrentYearQuarterNames ( DBTransaction dbTransaction ) throws ScalarException
	{
		int currentYear = Calendar.getInstance ( ).get ( Calendar.YEAR ) ;
		String quarters [] = new String[4];
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		boolean success = false;
		try
		{
			if ( dbTransaction == null )
			{
				conn = DataSource.getConnection ( );
			}
			else
			{
				conn = dbTransaction.getConnection ( );
			}
			pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.forecastEntrySearch.getQuarterNames" ) );
			pstmt.setInt ( 1, currentYear );
			rset = pstmt.executeQuery();
			int i = 0;
			while ( rset.next ( ) )
			{
				quarters [i] = new String  ( rset.getString ( "CQC_QUARTER" ) );
				i++;
			}
			success = true;
			if ( i == 0 )
			{
				System.out.println ( "----Current year company calendar was not found----" );
				throw new ScalarException ( "Current year company calendar was not found" );
			}
		}
		catch ( Exception e )
		{
			System.out.println ( "---Exception while getting the current year quarter names---" );
			throw new ScalarException ( "Could Not Get the QuarterNames", e );
		}
		finally
		{
			if ( dbTransaction == null )
			{
				if ( conn != null )
				{
					try
					{
						if ( success )
						{
							conn.commit ( );
						}
						else
						{
							conn.rollback ( );
						}
					}
					catch ( SQLException ex ) { }
				}
			}
			if ( rset != null )
			{
				try { rset.close(); } catch ( SQLException ignore ) { }
			}
			if ( pstmt != null )
			{
				try { pstmt.close(); } catch ( SQLException ignore ) { }
			}
			if ( conn != null )
			{
				try { conn.close(); } catch ( SQLException ignore ) { }
			}
		}
        return quarters;
	}
}
